const express = require('express');
const jwt = require('jsonwebtoken');
const mysql = require('mysql2/promise');

const app = express()

//开启json支持
app.use(express.json());

// 数据库连接池
const pool = mysql.createPool({
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'root',
    database: 'vue_admin_db'
});

// 认证中间件
const authenticateToken = (req, res, next) => {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1]
    if (token == null) {
        return res.sendStatus(401)
    } else {
        jwt.verify(token, 'secret_key', (err, user) => {
            if (err) {
                return res.sendStatus(403)
            }
            req.user = user;
            next()
        })
    }
}
// 获取菜单数据
app.get('/menu', authenticateToken, (req, res) => {
    const menuData = [
        {
            path: '/InfoPage/info',
            name: 'info',
            title: '信息管理',
            children: [
                {
                    path: '/InfoPage/Page1',
                    name: 'Page1',
                    title: '用户管理'
                },
                {
                    path: '/InfoPage/Page2',
                    name: 'Page2',
                    title: '数据统计'
                },
                {
                    path: '/InfoPage/Page3',
                    name: 'Page3',
                    title: '商品管理'
                },
                {
                    path: '/InfoPage/Page4',
                    name: 'Page4',
                    title: '订单管理'
                }
            ]
        }
    ];
    res.json(menuData);
});
//登录
app.post('/login', async (req, res) => {
    const { username, password } = req.body

    try { // 执行数据库查询，查找是否有匹配用户名和密码
        const [result] = await pool.query(
            'SELECT * FROM users WHERE username = ? AND password = ?',
            [username, password]
        )
        //  检查查询结果
        if (result.length > 0) {
            const token = jwt.sign(
                {id: result[0].id, username: result[0].username},
                'secret_key',
                {expiresIn: '24h'}
            );
            res.json({
                code: 200,
                message: '登录成功',
                accessToken: token,
                userInfo: {
                    username: result[0].username,  // 返回用户的用户名
                    name: result[0].name  // 返回用户的姓名
                }
            })
        } else {
            res.status(401).json({
                code: 401,
                message: '用户名或密码错误'
            });
        }
    } catch (e) {
        res.status(500).json({
            code: 500,
            message: e.message||'服务器错误'
        });
    }
})
//获取全部用户
app.get('/users',authenticateToken, async (req, res) => {
    //数据库查询，直接返回
    try {
        const [result] = await pool.query('SELECT * FROM users')
        // console.log('@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@',result)
        res.json(result)
    } catch (e) {
        res.status(500).json({
            code: 500,
            message: e.message||'服务器错误'
        });
    }
})
// 添加用户
app.post('/users', authenticateToken, async (req, res) => {
    try {
        const { name, age, address, phone } = req.body

        const result = await pool.query(
            'INSERT INTO users (name, age, address, phone) VALUES (?, ?, ?, ?)',
            [name, age, address, phone]
        )
        res.json({ message: '用户添加成功'})
    } catch (error) {
        console.error('添加用户错误:', error)
        res.status(500).json({
            code: 500,
            message: '服务器错误：' + error.message
        })
    }
})
//更新用户信息
app.put('/users/:id', authenticateToken, async (req, res) => {
    const userId = req.params.id
    const userData = req.body
    try { // 执行数据库操作，更新用户数据
        const [result] = await pool.query(
            `UPDATE users
             SET name = ?,
                 age = ?,
                 address = ?,
                 phone = ?
             WHERE id = ?`,
            [userData.name, userData.age, userData.address, userData.phone, userId]
        )
        //返回
        return res.status(200).json({
            code: 200,
            message: '更新成功'
        })
    } catch (error) {
        res.status(500).json({
            code: 500,
            message: '服务器错误：' + error.message
        })
    }
})
//删除用户
app.delete('/users/:id', authenticateToken, async (req, res) => {
    const userId = req.params.id
    //数据库执行
    try {
        const [result] = await pool.query(
            'DELETE FROM users WHERE id = ?',
            [userId]
        )
        return res.status(200).json({
            code: 200,
            message: '删除成功'
        })
    } catch (error) {
        return res.status(500).json({
            code: 500,
            message: '服务器错误：' + error.message
        })
    }


})
// 获取统计数据
app.get('/page2-data', authenticateToken, async (req, res) => {
    try {
        // 获取用户总数
        const [userTotal] = await pool.query('SELECT COUNT(*) as count FROM users');
        const totalUsers = userTotal[0].count;

        // 获取订单总数和待处理订单数
        //CASE 表达式
        const [orderStats] = await pool.query(`
            SELECT 
                COUNT(*) as total,
                
                SUM(CASE
                        WHEN status = 'pending' THEN 1
                        ELSE 0 
                    END) as pending
            
            FROM orders
        `);

        // 获取最近7天的订单数量统计
        //w%提取星期几，
        const [weeklyOrders] = await pool.query(`
            SELECT 
                DATE_FORMAT(create_time, '%w') as day,
                COUNT(*) as count
            FROM orders
            WHERE create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
            GROUP BY day
            ORDER BY day
        `);
        console.log('周数据为',weeklyOrders);
        // 构建长度为7的数组
        const weekData = Array(7).fill(0);//[0, 0, 0, 0, 0, 0, 0]
        weeklyOrders.forEach(item => {
            weekData[item.day] = item.count;
        });
        //weekData = [5, 8, 12, 0, 3, 6, 10];
        res.json({
            totalUsers,
            activeUsers: Math.floor(totalUsers * 0.8),
            totalOrders: orderStats[0].total,
            pendingOrders: orderStats[0].pending,
            weeklyStats: {
                labels: ['周日', '周一', '周二', '周三', '周四', '周五', '周六'],
                values: weekData
            }
        });
    } catch (error) {
        console.error('获取统计数据错误:', error);
        res.status(500).json({ message: '服务器错误' });
    }
});

//获取商品
app.get('/products', authenticateToken, async (req, res) => {
    try {
        const [products] = await pool.query('SELECT * FROM products ORDER BY created_at DESC')
        res.json({
            code: 200,
            message: '获取商品列表成功',
            data: products
        })
    } catch (error) {
        console.error('获取商品列表错误:', error)
        res.status(500).json({
            code: 500,
            message: '服务器错误：' + error.message
        })
    }
})
// 添加商品
app.post('/products', authenticateToken, async (req, res) => {
    try {
        const { name, category, price, stock } = req.body

        // 插入商品数据
        const [result] = await pool.query(
            'INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?)',
            [name, category, price, stock]
        )

        res.json({
            code: 200,
            message: '添加商品成功',
        })
    } catch (error) {
        console.error('添加商品错误:', error)
        res.status(500).json({
            code: 500,
            message: '服务器错误：' + error.message
        })
    }
})

// 更新商品
app.put('/products/:id', authenticateToken, async (req, res) => {
    try {
        const productId = req.params.id
        const { name, category, price, stock } = req.body

        // 更新商品数据
        const [result] = await pool.query(
            `UPDATE products 
             SET name = ?, category = ?, price = ?, stock = ? 
             WHERE id = ?`,
            [name, category, price, stock, productId]
        )
        res.json({
            code: 200,
            message: '更新商品成功',
        })
    } catch (error) {
        console.error('更新商品错误:', error)
        res.status(500).json({
            code: 500,
            message: '服务器错误：' + error.message
        })
    }
})

// 删除商品
app.delete('/products/:id', authenticateToken, async (req, res) => {
    try {
        const productId = req.params.id

        const [result] = await pool.query(
            'DELETE FROM products WHERE id = ?',
            [productId]
        )

        res.json({
            code: 200,
            message: '删除商品成功'
        })
    } catch (error) {
        console.error('删除商品错误:', error)
        res.status(500).json({
            code: 500,
            message: '服务器错误：' + error.message
        })
    }
})

app.get('/data', authenticateToken, async (req, res) => {
    try {
        const [result] = await pool.query(`
            SELECT category, COUNT(*) as count 
            FROM products 
            GROUP BY category
        `);

        const xAxisData = result.map(item => item.category);
        const seriesData = result.map(item => item.count);

        res.json({
            title: '商品类别统计',
            xAxisData,
            seriesData
        });
    } catch (error) {
        res.status(500).json({ message: '服务器错误' });
    }
});
// 获取订单列表
app.get('/orders', authenticateToken, async (req, res) => {
    try {
        const [result] = await pool.query(`
            SELECT 
                orders.id,
                orders.order_no as orderNo,
                orders.customer_name as customerName,
                orders.amount,
                orders.status,
                DATE_FORMAT(orders.create_time, '%Y-%m-%d %H:%i:%s') as createTime
            FROM orders
        `)
        
        res.json(result)
    } catch (error) {
        console.error('获取订单列表错误:', error)
        res.status(500).json({
            code: 500,
            message: '服务器错误：' + error.message
        })
    }
})

const PORT = process.env.PORT || 5050;
app.listen(PORT, () => {
    console.log(`Server is running on port ${PORT}`)
})

